﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class DatHangDAO
    {
        public DataSet LoadFromDAO(DateTime From, DateTime To)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT MaDH,NgayDat,TrangThai,GhiChu FROM DatHang WHERE NgayDat BETWEEN @F AND @T";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbParameter para = cmd.Parameters.Add("@F", OleDbType.DBDate);
            para.Value = From;
            OleDbParameter para2 = cmd.Parameters.Add("@T", OleDbType.DBDate);
            para2.Value = To;
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            connect.Close();
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            return ds;
        }

        public List<DatHangDTO> LayTatCaDatHang()
        {
            string sql = " SELECT MaDH,NgayDat,Diem,MaNV,TrangThai,GhiChu FROM DatHang ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<DatHangDTO> danhsach = new List<DatHangDTO>();

            while (reader.Read())
            {
                DatHangDTO dhDTO = new DatHangDTO();
                dhDTO.MaDH = reader.GetInt32(0);
                dhDTO.NgayDat = reader.GetDateTime(1);
                dhDTO.Diem = reader.GetInt32(2);
                dhDTO.MaNV = reader.GetInt32(3);
                dhDTO.TrangThai = reader.GetBoolean(4);
                dhDTO.GhiChu = reader.GetString(5);
                danhsach.Add(dhDTO);

            }
            conn.Close();
            return danhsach;

        }
        public int LoadMADH()
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT MAX(MaDH) FROM DatHang";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            object obj = cmd.ExecuteScalar();
            connect.Close();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            return kq;

        }
        public DataSet GetSanPham()
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT MaSP,TenSP,DonGiaTV,DonGiaKL,DiemCV FROM SanPham";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            connect.Close();
            return ds;
        }

        public DataSet DsSanpham(int id)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT CT.MaSP,SP.TenSP,CT.Diem, CT.DonGia,CT.SoLuong,CT.ThanhTien FROM SanPham SP,CTDatHang CT WHERE SP.MaSP=CT.MaSP AND CT.MaDH=@ID";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbParameter para = cmd.Parameters.Add("@ID", OleDbType.Integer);
            para.Value = id;
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            connect.Close();
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            return ds;
        }
        public int INSERTCTDH(CTDatHangDTO INPUT)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO CTDatHang VALUES(@MDH,@MSP,@ND,@SL,@DG,@TT,@DM,@CL)";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MDH", OleDbType.Integer);
            para.Value = INPUT.MaDH;
            para = cmd.Parameters.Add("@MSP", OleDbType.Integer);
            para.Value = INPUT.MaSP;
            para = cmd.Parameters.Add("@ND", OleDbType.Date);
            para.Value = INPUT.NgayDat;
            para = cmd.Parameters.Add("@SL", OleDbType.Integer);
            para.Value = INPUT.SoLuong;
            para = cmd.Parameters.Add("@DG", OleDbType.Double);
            para.Value = INPUT.DonGia;
            para = cmd.Parameters.Add("@TT", OleDbType.Double);
            para.Value = INPUT.ThanhTien;
            para = cmd.Parameters.Add("@DM", OleDbType.Integer);
            para.Value = INPUT.Diem;
            para = cmd.Parameters.Add("@CL", OleDbType.Integer);
            para.Value = INPUT.ConLai;

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int INSERTDH(DatHangDTO INPUT)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO DatHang VALUES(@MaDH,@NgayDH,@MaNV,@TrangThai,@GhiChu,@CK)";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MADH", OleDbType.Integer);
            para.Value = INPUT.MaDH;
            para = cmd.Parameters.Add("@NgayDH", OleDbType.Date);
            para.Value = INPUT.NgayDat;
            para = cmd.Parameters.Add("@MANV", OleDbType.Integer);
            para.Value = INPUT.MaNV;
            para = cmd.Parameters.Add("TrangThai", OleDbType.Boolean);
            para.Value = INPUT.TrangThai;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = INPUT.GhiChu;
             para = cmd.Parameters.Add("@CK", OleDbType.Integer);
             para.Value = INPUT.Diem;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int DeleteCTDH(int id)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "DELETE FROM CTDatHang WHERE MaDH=" + id.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public DataSet SELECTTENNV()
        {
         OleDbConnection connect = DataProvider.ConnectDB();
         string sql = "SELECT HoTen From NhanVien";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            connect.Close();
            return ds;
        }
        public int RETURNMANV(string INPUT)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT MaNV From NhanVien WHERE HoTen LIKE'%"+INPUT+"%'";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            int kq = cmd.ExecuteNonQuery();
            connect.Close();
            if (kq >= 0)
                return kq;
            else
                return -1;
        }
        public DataSet RETURNTENNV(int INPUT)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT HoTen From NhanVien WHERE MaNV=@M";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbParameter para = cmd.Parameters.Add("@M", OleDbType.Integer);
            para.Value = INPUT;
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            connect.Close();
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            return ds;
        }
        public int DeleteDH(int id)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "DELETE FROM DatHang WHERE MaDH=" + id.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return -1;
        }
        public DataSet LOADDH(int k)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT * From DatHang WHERE MaDH="+k.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            connect.Close();
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            return ds;
        }
        public DataSet FINDSP(int k)
        {
            OleDbConnection connect = DataProvider.ConnectDB();
            string sql = "SELECT  TenSP,DonGiaTV,DiemCV From SanPham WHERE MaSP=" + k.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataAdapter Adt = new OleDbDataAdapter(cmd);
            connect.Close();
            DataSet ds = new DataSet();
            Adt.Fill(ds);
            return ds;
        }
        public int CountDH()
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "DELETE Count(*) FROM DatHang";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return kq;
            else
                return -1;
        }
    }
}
